import { Service } from 'egg';
import { UpdateTeacherSchedule } from '../../model/teacher_schedule';
import { UserRole } from '../../model/user';

export default class DbTeacherScheduleService extends Service {

    public async getTeacherSchedulesByTeacherId(teacherId: number) {
        const mysql = this.app.mysql;

        // 查询当前用户
        return await mysql.select('teacher_schedule', {
            where: {
                teacher_id: teacherId,
            },
            orders: [['day', 'asc'], ['time_from', 'asc']],
        });
    }

    public async getTeacherSchdules(teacherIds: number[]) {
        const mysql = this.app.mysql;
        if (!teacherIds || teacherIds.length <= 0) {
            return await mysql.query(`SELECT
                                    u.id as teacher_id,
                                    ts.id as schedule_id,
                                    u.username as teacher_name,
                                    ts.day as day,
                                    ts.time_from as time_from,
                                    ts.time_to as time_to
                                FROM user as u
                                LEFT JOIN teacher_schedule as ts ON u.id = ts.teacher_id
                                WHERE u.role = ${UserRole.Teacher}
                                ORDER BY u.id ASC, ts.day ASC, ts.time_from ASC, ts.time_to ASC`,
            );
        }
        // 查询当前用户
        return await mysql.query(`SELECT
                                    u.id as teacher_id,
                                    ts.id as schedule_id,
                                    u.username as teacher_name,
                                    ts.day as day,
                                    ts.time_from as time_from,
                                    ts.time_to as time_to
                                FROM user as u
                                LEFT JOIN teacher_schedule as ts ON u.id = ts.teacher_id
                                WHERE u.role = ${UserRole.Teacher} AND u.id in (${teacherIds.join(',')})
                                ORDER BY u.id ASC, ts.day ASC, ts.time_from ASC, ts.time_to ASC`,
            );
    }

    public async removeSchedules(teacherId: number, conn?: any) {
        const mysql = conn ? conn : this.app.mysql;
        await mysql.delete('teacher_schedule', { teacher_id : teacherId});
    }

    public async addSchedules(teacherId: number, teacherSchedules: UpdateTeacherSchedule[], conn?: any) {
        const mysql = conn ? conn : this.app.mysql;
        for (const schedule of teacherSchedules) {
            for (const timeSpan of schedule.time_spans) {
                await mysql.insert('teacher_schedule', {
                    day: schedule.day,
                    teacher_id : teacherId,
                    time_from: timeSpan.time_from,
                    time_to: timeSpan.time_to,
                });
            }
        }
    }

    public async checkoutInTeacherSchedule(day: number, timeFrom: string, timeTo: string) {
        const sql =
            `SELECT count(*) FROM schedule.teacher_schedule
            WHERE day = ? AND time_from <= ? AND time_to >= ?;`;
        const mysql = this.app.mysql;
        const result = await mysql.queryOne(sql, [day, timeFrom, timeTo]);
        return result['count(*)'] > 0;
    }
}
